package org.jeecg.modules.demo.pcl.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.jeecg.modules.demo.pcl.entity.MesAlmTpmcount;
import org.jeecg.modules.demo.pcl.mapper.MesAlmTpmcountMapper;
import org.jeecg.modules.demo.pcl.service.IMesAlmTpmcountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collections;
import java.util.Date;
import java.util.List;

/**
 * @Description: mes-tpm-评分均值
 * @Author: jeecg-boot
 * @Date:   2025-02-18
 * @Version: V1.0
 */
@Service
@DS("mes-alm-datasource")
public class MesAlmTpmcountServiceImpl extends ServiceImpl<MesAlmTpmcountMapper, MesAlmTpmcount> implements IMesAlmTpmcountService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public IPage<MesAlmTpmcount> getCustomPageList(Integer pageNo, Integer pageSize, String belongMonth) throws ParseException {
        // 使用SimpleDateFormat来解析belongMonth
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
            // 将belongMonth字符串解析为Date对象
            Date date = sdf.parse(belongMonth);
            // 格式化为"yyyy-MM"格式
            String formattedMonth = new SimpleDateFormat("yyyy-MM").format(date);

            String sql = "SELECT " +
                    "a.wo_name, " +
                    "ROUND(AVG(CASE WHEN ss.act_value <= 50 THEN ss.act_value END) * 4, 2) AS average_act_value, " +
                    "CASE " +
                    "    WHEN COUNT(CASE WHEN ss.act_value <= 50 THEN ss.act_value END) / 4 < 3 THEN '样本过少:月最少需3天记录' " +
                    "    WHEN ROUND(AVG(CASE WHEN ss.act_value <= 50 THEN ss.act_value END) * 4, 2) > 100 THEN '平均值过高:请检查打分是否错误' " +
                    "    ELSE NULL " +
                    "END AS message, " +
                    "ROUND(COUNT(CASE WHEN ss.act_value <= 50 THEN ss.act_value END) / 4) AS count_num " +
                    "FROM " +
                    "amtc_wo a " +
                    "LEFT JOIN " +
                    "amtc_wo_checklist ss ON ss.wo_id = a.wo_id " +
                    "WHERE " +
                    "a.wo_type_id = 25 " +
                    "AND DATE_FORMAT(a.target_start_date, '%Y-%m') = '" + formattedMonth + "' " +
                    "AND ss.wo_id IS NOT NULL " +
                    "GROUP BY " +
                    "a.wo_name";
            Page<MesAlmTpmcount> page = new Page<>(pageNo, pageSize);
            List<MesAlmTpmcount> records = jdbcTemplate.query(sql, (rs, rowNum) -> {
                MesAlmTpmcount custom = new MesAlmTpmcount();
                custom.setWoName(rs.getString("wo_name"));
                custom.setAverageActValue(String.valueOf(rs.getDouble("average_act_value")));
                custom.setMessage(rs.getString("message"));
                custom.setCountNum(String.valueOf(rs.getInt("count_num")));
                return custom;
            });

            page.setRecords(records);
            page.setTotal(records.size()); // 这里假设所有记录都返回，实际需要根据SQL查询总数
            return page;
    }

    @Override
    public List<MesAlmTpmcount> getCustomList(String belongMonth) throws ParseException {
        // 使用SimpleDateFormat来解析belongMonth
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        // 将belongMonth字符串解析为Date对象
        Date date = sdf.parse(belongMonth);
        // 格式化为"yyyy-MM"格式
        String formattedMonth = new SimpleDateFormat("yyyy-MM").format(date);
        String sql = "SELECT " +
                "a.wo_name, " +
                "ROUND(AVG(CASE WHEN ss.act_value <= 50 THEN ss.act_value END) * 4, 2) AS average_act_value, " +
                "CASE " +
                "    WHEN COUNT(CASE WHEN ss.act_value <= 50 THEN ss.act_value END) / 4 < 3 THEN '样本过少:月最少需3天记录' " +
                "    WHEN ROUND(AVG(CASE WHEN ss.act_value <= 50 THEN ss.act_value END) * 4, 2) > 100 THEN '平均值过高:请检查打分是否错误' " +
                "    ELSE NULL " +
                "END AS message, " +
                "ROUND(COUNT(CASE WHEN ss.act_value <= 50 THEN ss.act_value END) / 4) AS count_num " +
                "FROM " +
                "amtc_wo a " +
                "LEFT JOIN " +
                "amtc_wo_checklist ss ON ss.wo_id = a.wo_id " +
                "WHERE " +
                "a.wo_type_id = 25 " +
                "AND DATE_FORMAT(a.target_start_date, '%Y-%m') = '" + formattedMonth + "' " +
                "AND ss.wo_id IS NOT NULL " +
                "GROUP BY " +
                "a.wo_name";
        List<MesAlmTpmcount> records = jdbcTemplate.query(sql, (rs, rowNum) -> {
            MesAlmTpmcount custom = new MesAlmTpmcount();
            custom.setWoName(rs.getString("wo_name"));
            custom.setAverageActValue(String.valueOf(rs.getDouble("average_act_value")));
            custom.setMessage(rs.getString("message"));
            custom.setCountNum(String.valueOf(rs.getInt("count_num")));
            return custom;
        });
        return records;
    }
}
